package com.example.liuxiangyun;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.liuxiangyun.bean.Course;
import com.example.liuxiangyun.bean.Student;

import java.util.ArrayList;
import java.util.List;

public class DatabaseOpenHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "user_database";
    private static final int DATABASE_VERSION = 4;

//user表的表名和列名
    private static final String TABLE_USERS = "users";
    private static final String COLUMN_ID = "_id";
    private static final String COLUMN_USERNAME = "username";
    private static final String COLUMN_PASSWORD = "password";
    private static final String COLUMN_NAME = "name";


    //student表的表名和列名
    private static final String TABLE_STUDENTS = "students";
    private static final String COLUMN_STUDENT_ID = "student_id";
    private static final String COLUMN_STUDENT_NAME = "student_name";
    private static final String COLUMN_STUDENT_NUMBER = "student_number";
    private static final String COLUMN_CLASS = "class";


    //course表的表名和列名
    private static final String TABLE_COURSES = "courses";
    private static final String COLUMN_COURSE_ID = "course_id";
    private static final String COLUMN_COURSE_NAME = "course_name";
    private static final String COLUMN_TEACHER_NAME = "teacher_name";
    private static final String COLUMN_CLASS_TIME = "class_time";
    private static final String COLUMN_CLASS_LOCATION = "class_location";


    // 创建用户表的 SQL 语句
    private static final String TABLE_CREATE_USERS =
            "CREATE TABLE " + TABLE_USERS + " (" +
                    COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_USERNAME + " TEXT, " +
                    COLUMN_PASSWORD + " TEXT, " +
                    COLUMN_NAME + " TEXT);";


        // 创建学生表的 SQL 语句
        private static final String TABLE_CREATE_STUDENTS =
                "CREATE TABLE " + TABLE_STUDENTS + " (" +
                        COLUMN_STUDENT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        COLUMN_STUDENT_NAME + " TEXT, " +
                        COLUMN_STUDENT_NUMBER + " TEXT, " +
                        COLUMN_CLASS + " TEXT, " +
                        COLUMN_USERNAME + " TEXT);";

        // 创建课程表的 SQL 语句
        private static final String TABLE_CREATE_COURSES =
                "CREATE TABLE " + TABLE_COURSES + " (" +
                        COLUMN_COURSE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        COLUMN_COURSE_NAME + " TEXT, " +
                        COLUMN_TEACHER_NAME + " TEXT, " +
                        COLUMN_CLASS_TIME + " TEXT, " +
                        COLUMN_CLASS_LOCATION + " TEXT);";

        public DatabaseOpenHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(TABLE_CREATE_USERS);
            db.execSQL(TABLE_CREATE_STUDENTS);
            db.execSQL(TABLE_CREATE_COURSES);
        }
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_STUDENTS);
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_COURSES);
            onCreate(db);
        }

        // 注册新用户
        public boolean registerUser(String username, String password, String name) {
            SQLiteDatabase db = this.getWritableDatabase();
            ContentValues values = new ContentValues();
            values.put(COLUMN_USERNAME, username);
            values.put(COLUMN_PASSWORD, password);
            values.put(COLUMN_NAME, name);

            long result = db.insert(TABLE_USERS, null, values);
            db.close();

            return result != -1;
        }

        // 登录验证
        public boolean loginUser(String username, String password) {
            SQLiteDatabase db = this.getReadableDatabase();
            String[] columns = {COLUMN_ID};
            String selection = COLUMN_USERNAME + "=? AND " + COLUMN_PASSWORD + "=?";
            String[] selectionArgs = {username, password};
            Cursor cursor = db.query(TABLE_USERS, columns, selection, selectionArgs, null, null, null);

            boolean isValidUser = cursor.moveToFirst();
            cursor.close();
            db.close();

            return isValidUser;
        }
    // 添加课程
    public boolean addCourse(Course course) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_COURSE_NAME, course.getCourseName());
        values.put(COLUMN_TEACHER_NAME, course.getTeacherName());
        values.put(COLUMN_CLASS_TIME, course.getClassTime());
        values.put(COLUMN_CLASS_LOCATION, course.getClassLocation());

        long result = db.insert(TABLE_COURSES, null, values);
        db.close();
        return result != -1;
    }

    // 查询所有课程，转换为 Course 对象
    public List<Course> getAllCourses() {
        List<Course> courses = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_COURSES, null, null, null, null, null, null);
        while (cursor.moveToNext()) {
            @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex(COLUMN_COURSE_ID));
            @SuppressLint("Range") String courseName = cursor.getString(cursor.getColumnIndex(COLUMN_COURSE_NAME));
            @SuppressLint("Range") String teacherName = cursor.getString(cursor.getColumnIndex(COLUMN_TEACHER_NAME));
            @SuppressLint("Range") String classTime = cursor.getString(cursor.getColumnIndex(COLUMN_CLASS_TIME));
            @SuppressLint("Range") String classLocation = cursor.getString(cursor.getColumnIndex(COLUMN_CLASS_LOCATION));

            courses.add(new Course(id, courseName, teacherName, classTime, classLocation));
        }
        cursor.close();
        db.close();
        return courses;
    }

    // 添加学生
    public boolean addStudent(Student student) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values1 = new ContentValues();
        values1.put(COLUMN_USERNAME, student.getStudentNumber());
        values1.put(COLUMN_PASSWORD,"123456");
        db.insert(TABLE_USERS, null, values1);

        ContentValues values = new ContentValues();
        values.put(COLUMN_STUDENT_NAME, student.getName());
        values.put(COLUMN_STUDENT_NUMBER, student.getStudentNumber());



        values.put(COLUMN_CLASS, student.getClassName());
        values.put(COLUMN_USERNAME, student.getUsername());

        long result = db.insert(TABLE_STUDENTS, null, values);
        db.close();
        return result != -1;
    }

    // 查询所有学生，转换为 Student 对象
    public List<Student> getAllStudents() {
        List<Student> students = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_STUDENTS, null, null, null, null, null, null);
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex(COLUMN_STUDENT_ID));
            String name = cursor.getString(cursor.getColumnIndex(COLUMN_STUDENT_NAME));
            String studentNumber = cursor.getString(cursor.getColumnIndex(COLUMN_STUDENT_NUMBER));
            String className = cursor.getString(cursor.getColumnIndex(COLUMN_CLASS));
            String username = cursor.getString(cursor.getColumnIndex(COLUMN_USERNAME));

            students.add(new Student(id, name, studentNumber, username, className));
        }
        cursor.close();
        db.close();
        return students;
    }

    //传入用户名 旧密码 新密码 修改密码
    public boolean changePassword(String username, String oldPassword, String newPassword) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_PASSWORD, newPassword);
        String whereClause = COLUMN_USERNAME + "=? AND " + COLUMN_PASSWORD + "=?";
        String[] whereArgs = {username, oldPassword};
        int result = db.update(TABLE_USERS, values, whereClause, whereArgs);
        db.close();
        return result > 0;
    }
}
